package com.dao.impl;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.dao.TeachDAO;
import com.dto.TeachDTO;
import com.util.db.DBUtil;

public class TeachDAOImpl implements TeachDAO {
	private Connection conn;

	public TeachDAOImpl() {
		conn = DBUtil.getConnection();
	}

	@Override
	public TeachDTO getTeach(String t_id, String c_id) {
		TeachDTO teachDTO = null;
		String sql = "SELECT * FROM t_base_teach_info WHERE t_id=? AND c_id=?;";
		QueryRunner qr = new QueryRunner();

		try {
			teachDTO = qr.query(conn, sql, new BeanHandler<TeachDTO>(
					TeachDTO.class), t_id, c_id);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				DbUtils.close(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return teachDTO;
	}

	@Override
	public List<TeachDTO> getTeach(String t_id) {
		List<TeachDTO> teaches = null;
		String sql = "SELECT * FROM t_base_teach_info WHERE t_id=?;";
		QueryRunner qr = new QueryRunner();

		try {
			teaches = qr.query(conn, sql, new BeanListHandler<TeachDTO>(
					TeachDTO.class), t_id);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				DbUtils.close(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return teaches;
	}

	@Override
	public List<TeachDTO> getTeaches() {
		List<TeachDTO> teaches = null;
		String sql = "SELECT * FROM t_base_teach_info;";
		QueryRunner qr = new QueryRunner();

		try {
			teaches = qr.query(conn, sql, new BeanListHandler<TeachDTO>(
					TeachDTO.class));
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				DbUtils.close(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return teaches;
	}

	@Override
	public TeachDTO getTeachById(String t_id) {
		TeachDTO teach = null;
		String sql = "SELECT * FROM t_base_teach_info WHERE t_id=?;";
		QueryRunner qr = new QueryRunner();

		try {
			teach = qr.query(conn, sql, new BeanHandler<TeachDTO>(
					TeachDTO.class), t_id);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				DbUtils.close(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return teach;
	}

	@Override
	public List<String> getCourseId(String t_id) {
		List<TeachDTO> teaches = null;
		List<String> course_ids = null;
		String sql = "SELECT * FROM t_base_teach_info WHERE t_id=?;";
		QueryRunner qr = new QueryRunner();

		try {
			teaches = qr.query(conn, sql, new BeanListHandler<TeachDTO>(
					TeachDTO.class), t_id);

			if (teaches.size() > 0) {
				course_ids = new ArrayList<String>();
				for (int i = 0; i < teaches.size(); i++) {
					course_ids.add(teaches.get(i).getC_id());
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				DbUtils.close(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return course_ids;
	}

	@Override
	public String getTeacherId(String c_id) {
		TeachDTO teach = null;
		String sql = "SELECT * FROM t_base_teach_info WHERE c_id=?;";
		QueryRunner qr = new QueryRunner();

		try {
			teach = qr.query(conn, sql, new BeanHandler<TeachDTO>(
					TeachDTO.class), c_id);

			if (teach != null) {
				return teach.getT_id();
			}
		} catch (SQLException e) {
			return null;
		} finally {
			try {
				DbUtils.close(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}

	@Override
	public boolean saveTeach(TeachDTO teachDTO) {
		boolean result = true;
		int insertRows = 0;
		String sql = "INSERT INTO t_base_teach_info VALUES(?, ?, ?, ?);";
		QueryRunner qr = new QueryRunner();

		Object[] params = new Object[4];
		params[0] = teachDTO.getT_id();
		params[1] = teachDTO.getC_id();
		params[2] = teachDTO.getTerm();
		params[3] = teachDTO.getRemark();

		try {
			insertRows = qr.update(conn, sql, params);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				DbUtils.close(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		result = (insertRows == 0) ? false : true;
		return result;
	}

	@Override
	public List<TeachDTO> getTeachesById(String t_id) {
		List<TeachDTO> teaches = null;
		String sql = "SELECT * FROM t_base_teach_info WHERE t_id=?;";
		QueryRunner qr = new QueryRunner();

		try {
			teaches = qr.query(conn, sql, new BeanListHandler<TeachDTO>(
					TeachDTO.class), t_id);
		} catch (SQLException e) {
			return null;
		} finally {
			try {
				DbUtils.close(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return teaches;
	}

	@Override
	public boolean removeLink(String t_id, String c_id) {
		boolean result = true;
		int removeRows = 0;
		String sql = "DELETE FROM t_base_teach_info WHERE t_id=? AND c_id=?;";
		QueryRunner qr = new QueryRunner();

		try {
			removeRows = qr.update(conn, sql, t_id, c_id);
		} catch (SQLException e) {
			return false;
		} finally {
			try {
				DbUtils.close(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		result = (removeRows == 0) ? false : true;
		return result;
	}
}
